package com.wanmait.movies.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.wanmait.movies.util.DBHelper;

public class GiveGradesDao {
	private GiveGradesDao() {}
	private static GiveGradesDao giveGradesDao = new GiveGradesDao();
	public static GiveGradesDao getObj() {
		return giveGradesDao;
	}
	
	//���Ѵ���ֵ��û��������µĴ�������ݿ����޸�
	public void xiugaidafen(int userId,int fenshu,int movieId) {
		Connection conn = DBHelper.getDBHelper().getconn();
//		update giveGrades set grades=? where movie_id=? and user_id=?
		String sql="update giveGrades set grades=? where movie_id=? and user_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,fenshu);
			ps.setInt(2, movieId);
			ps.setInt(3, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
	}
	
	//�жϸ��û��Ըõ�Ӱ�Ƿ�����
	public boolean ifDaFen(int userId,int movieId ) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="select *  from giveGrades where user_id=? and movie_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,movieId);
			ps.setInt(2, userId);
			rs=ps.executeQuery();
			
			if(rs.next()) {
				flag=true;//���ص���true�����û��Ѵ����
			}
			else {
				flag=false;//���ص���false�����û�û�д����
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return flag;
		
	}
	//�����ݿ����õ��õ�Ӱ��ƽ����
	public double getfenshu(int movieId) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="select avg(grades) as fenshu  from giveGrades where movie_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		double i=0;
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, movieId);
			rs=ps.executeQuery();
			if(rs.next()) {
				i=rs.getDouble("fenshu");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return i;
		
	}
	
	//���û�����Ӱ��ķ����ϴ������ݿ�
	public void dafen(int userId,int fenshu,int movieId) {
		Connection conn = DBHelper.getDBHelper().getconn();
		String sql="insert into giveGrades(movie_id,grades,user_id) values(?,?,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,movieId);
			ps.setInt(2, fenshu);
			ps.setInt(3, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
	}
	
	//删除分数
	
	public void deleteGrades(int id) {
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql ="delete from givegrades  where movie_id=?";
		try {
			
			ps=co.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
		DBHelper.getDBHelper().close(rs, ps, co);
		}
	}
}
